﻿//using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using Office = Microsoft.Office.Core;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Drawing;
using DevExpress.Web.ASPxPivotGrid;
using DevExpress.Data.PivotGrid;
using DevExpress.Utils;

public partial class UserControl_ControlBaoCaoDoanhSoNgay : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Session["db"] != null)
            {
                DataTable db = (DataTable)Session["db"];
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
            }
        }
        else
        {
            //LoadMonthLy();
            DateTime getFirstday=new DateTime (DateTime.Now.Year, DateTime.Now.Month, 1);
            txtFromDate.Text = getFirstday.ToString("dd/MM/yyyy");
            txtToDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
        }
    }
    void ShowMessageBox(string text)
    {
        MessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
             DataTable db=null;

             bool kq = dt.ExecuteSQl_bool(str, CommandType.StoredProcedure, "ls_genDailyOfftake_Fimex", ref error,
                 new SqlParameter("@userName", Session["user"].ToString()),
                 new SqlParameter("@frDdMmYyyy", txtFromDate.Text.ToString()),
                 new SqlParameter("@toDdMmYyyy", txtToDate.Text.ToString()));

             db = dt.Get_Data_DataTable(str, ref error, "ls_getDailyOfftake_Fimex", CommandType.StoredProcedure,
                     new SqlParameter("@userName", Session["user"].ToString()));
            
            if (db.Rows.Count > 0)
            {

                ASPxPivotGrid1.DataSource = db;
               Session["db"] = db;
            }
            else
            {
                if (Session["db"] != null)
                {
                    Session["db"] = null;
                }
                ASPxPivotGrid1.DataSource = null;
                ShowMessageBox("Dữ liệu không có");
            }
            ASPxPivotGrid1.DataBind();
            
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {
                ASPxPivotGridExporter1.OptionsPrint.PrintFilterHeaders = DefaultBoolean.False;
                ASPxPivotGridExporter1.OptionsPrint.PrintColumnHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintRowHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintDataHeaders = DefaultBoolean.False;

                string contentType = "application/ms-excel";
                string fileName = "DoanhSoNgay_"+txtFromDate.Text.ToString().Replace("/","")+"_"+txtToDate.Text.ToString().Replace("/","")+".xls";
                
                //string fileName = "DoanhSoNhapHang_";// +DropMonthy.SelectedValue.ToString() + "_" + DropCompare.SelectedValue.ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                ASPxPivotGridExporter1.ExportToXls(filestream);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch
        {

        }
    }
    
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
    protected void imgBtnPivot_Click(object sender, ImageClickEventArgs e)
        {
            generatePivotExcel();
        }

    void generatePivotExcel()
    {
        
        string fileName = Session["user"].ToString() + "_DoanhSoNgay.xls";

        if (File.Exists(Server.MapPath(fileName)))
        {
            File.Delete(Server.MapPath(fileName));
        }


        string connection = @"OLEDB;Provider=SQLOLEDB.1;" + str;


        string cmd = "SELECT     InvDate, TinhThanh, QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ReportTo, NVBH, Customer, MaHang, GAM, GroupSKU, sum(SoLuong) as SoLuong, sum(DSoTruocThue) as DSoTruocThue, Sum(DSoSauThue) as DSoSauThue,TinhDoanhSoCho FROM dml_" + Session["user"].ToString() + "_vwDailyOfftake Group By InvDate, TinhThanh, QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ReportTo, NVBH, Customer, MaHang, GAM, GroupSKU, TinhDoanhSoCho ";
        Excel.Application excel = new Excel.Application();
        try
        {
            excel.Visible = false;
            excel.AlertBeforeOverwriting = false;
            object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };
            Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
            style.Font.Bold = true;
            style.Font.Size = 32;
            sheet.Name = "BaoCaoDoanhSoNgay";
            sheet.Activate();

            foreach (Excel.Worksheet sh in workbook.Worksheets)
            {
                if (sh.Name.ToString() != "BaoCaoDoanhSoNgay")
                {
                    excel.DisplayAlerts = false;
                    sh.Delete();
                }
            }

            Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

            pivotCache.Connection = connection;
            pivotCache.MaintainConnection = true;




            pivotCache.CommandText = cmd;
            pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
            Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
            Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[18, 2], "PivotTable11", Missing.Value, Missing.Value);


            //, TinhThanh, QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("InvDate");
            pageField1.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField1.Caption = "Ngày";
            

            //, , QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField2 = (Excel.PivotField)pivotTable.PivotFields("TinhThanh");
            pageField2.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField2.Caption = "Tỉnh thành";
            

            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField3 = (Excel.PivotField)pivotTable.PivotFields("QuanHuyen");
            pageField3.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField3.Caption = "Quận huyện";
            pageField3.set_Subtotals(1, noSub);
            
            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField30 = (Excel.PivotField)pivotTable.PivotFields("TinhDoanhSoCho");
            pageField30.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField30.Caption = "Tính doanh số cho";
            pageField30.set_Subtotals(1, noSub);

            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("MaHangTheoNPP");
            pageField4.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField4.Caption = "Tên hàng theo NPP";
            pageField4.set_Subtotals(1, noSub);

            //SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("Chanel");
            pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField5.Caption = "Kênh";
            

            //, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField6 = (Excel.PivotField)pivotTable.PivotFields("SubChanel");
            pageField6.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField6.Caption = "Sub Chanel";
            pageField6.set_Subtotals(1, noSub);

            //, , ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField06 = (Excel.PivotField)pivotTable.PivotFields("NhaPhanPhoi");
            pageField06.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField06.Caption = "Nhà phân phối";
            

            //, , , TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField7 = (Excel.PivotField)pivotTable.PivotFields("ReportTo");
            pageField7.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField7.Caption = "Báo cáo cho";
            

            //, , , , , Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField9 = (Excel.PivotField)pivotTable.PivotFields("NVBH");
            pageField9.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField9.Caption = "Nhân viên bán hàng";
            

            //, , , , , , MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField10 = (Excel.PivotField)pivotTable.PivotFields("Customer");
            pageField10.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField10.Caption = "Khách hàng";
            pageField10.set_Subtotals(1, noSub);

            //, , , , , , , GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField11 = (Excel.PivotField)pivotTable.PivotFields("MaHang");
            pageField11.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField11.Caption = "Tên hàng theo Fimex";
            pageField11.set_Subtotals(1, noSub);

            // GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField12 = (Excel.PivotField)pivotTable.PivotFields("GAM");
            pageField12.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField12.Caption = "GAM";
            pageField12.set_Subtotals(1, noSub);

            // , , , 
            Excel.PivotField pageField13 = (Excel.PivotField)pivotTable.PivotFields("GroupSKU");
            pageField13.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField13.Caption = "Group";
            pageField13.set_Subtotals(1, noSub);

            
            pivotTable.AddDataField(pivotTable.PivotFields("SoLuong"), "Số lượng", Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("DSoTruocThue"), "Trước thuế", Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("DSoSauThue"), "Sau thuế", Excel.XlConsolidationFunction.xlSum);
            pivotTable.RowGrand = false;
            pivotTable.ColumnGrand = true;
            pivotTable.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            Excel.Range rg = pivotTable.DataBodyRange;
            rg.NumberFormat = "#,##0.00";

            sheet.Cells.EntireColumn.AutoFit();

            Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO DOANH SỐ NGÀY";

            Excel.Range range2 = ((Excel.Range)sheet.Cells[2, 1]);
            range2.Value2 = "Từ ngày " + txtFromDate.Text.ToString() + " đến ngày " + txtToDate.Text.ToString();

            Excel.Range range1 = ((Excel.Range)sheet.Cells[3, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();



            workbook.SaveAs(Server.MapPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workbook.Close(true, Missing.Value, Missing.Value);
            excel.Quit();

            saveFile(this.Response, Server.MapPath(fileName));

        }
        catch
        {

        }
        finally
        {
            // Make sure we release the reference to the underlying COM object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
        }
    }



    protected void imgBtnClearFilter_Click(object sender, ImageClickEventArgs e)
    {
        for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
        {
            ASPxPivotGrid1.Fields[i].FilterValues.Clear();
        }
    }
}



